/***********************************************************************************************************************************
 * Name:	Trace_LoginAudit.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Create a server-side trace.
 */
SET IMPLICIT_TRANSACTIONS OFF; WHILE 0 < @@TRANCOUNT ROLLBACK;
USE master;
GO

DECLARE
  @TraceID		INT,
  @TraceFile	NVARCHAR(245)	= master.DBAdmin.ErrorLogDirectory() + REPLACE(@@SERVERNAME,'\','!') + '_LoginAudit',
  @StopTime		DATETIME		= DATEADD(wk,1,GETDATE()),	-- 1 Week
-- variable declarations required for data type
  @MaxFileSize	BIGINT			= 128,
  @On			BIT				= 1;

-- create the trace ============================================================
EXECUTE sp_trace_create
	@TraceID	= @TraceID OUTPUT,
	@options	= 2,				-- TRACE_FILE_ROLLOVER
	@tracefile	= @TraceFile,
	@maxfilesize= @MaxFileSize,
	@stoptime	= @StopTime,
	@filecount	= 8;				-- 8 filex x 128MB = 1GB Max
/*
 *	Return	Description
 *	0		No error.
 *	1		Unknown error.
 *	10		Invalid options. Returned when options specified are incompatible.
 *	12		File not created.
 *	13		Out of memory. Returned when there is not enough memory to perform the specified action.
 *	14		Invalid stop time. Returned when the stop time specified has already happened.
 *	15		Invalid parameters. Returned when the user supplied incompatible parameters.
 */

-- set the events ==============================================================

--Audit Login (14) 
EXECUTE sp_trace_setevent @TraceID, 14,  6, @On;	-- NTUserName
EXECUTE sp_trace_setevent @TraceID, 14,  7, @On;	-- NTDomainName
EXECUTE sp_trace_setevent @TraceID, 14,  8, @On;	-- HostName
EXECUTE sp_trace_setevent @TraceID, 14,  9, @On;	-- ClientProcessID
EXECUTE sp_trace_setevent @TraceID, 14, 10, @On;	-- ApplicationName
EXECUTE sp_trace_setevent @TraceID, 14, 11, @On;	-- LoginName
EXECUTE sp_trace_setevent @TraceID, 14, 14, @On;	-- StartTime

--ExistingConnection (17)
EXECUTE sp_trace_setevent @TraceID, 17,  6, @On;	-- NTUserName
EXECUTE sp_trace_setevent @TraceID, 17,  7, @On;	-- NTDomainName
EXECUTE sp_trace_setevent @TraceID, 17,  8, @On;	-- HostName
EXECUTE sp_trace_setevent @TraceID, 17,  9, @On;	-- ClientProcessID
EXECUTE sp_trace_setevent @TraceID, 17, 10, @On;	-- ApplicationName
EXECUTE sp_trace_setevent @TraceID, 17, 11, @On;	-- LoginName
EXECUTE sp_trace_setevent @TraceID, 17, 14, @On;	-- StartTime

-- filter out known service account logins
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 1, N'BBAAviation\LiteSpeed';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 1, N'BBAAviation\QSLoS';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 7, N'BBAAviation\%$';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 7, N'BBAAviation\%BRMAgent';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 7, N'BBAAviation\BBAOrlETL%';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 7, N'BBAAviation\BBAOrlReports%';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 7, N'BBAAviation\Repl%Service';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 7, N'BBAAviation\SQL%Agent';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 7, N'BBAAviation\SQL%Analysis';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 7, N'BBAAviation\SQL%Browser';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 7, N'BBAAviation\SQL%Service';
EXECUTE sp_trace_setfilter @TraceID, 11, 0, 7, N'BBAAviation\SSRS%Service';

-- start the trace
EXECUTE sp_trace_setstatus @TraceID, 1;
GO

-- check trace status
DECLARE @property TABLE (PropertyID INT NOT Null PRIMARY KEY CLUSTERED, PropertyText VARCHAR(9) NOT Null);
INSERT INTO @property VALUES (1,'Options'),(2,'File Name'),(3,'Max Size'),(4,'Stop Time'),(5,'Status');
SELECT TraceID, PropertyText AS Property, Value FROM fn_trace_getinfo(default) i INNER JOIN @property p ON i.Property = p.PropertyID;
GO

-- stop the trace
/*
DECLARE @TraceID INT= 0;
EXECUTE sp_trace_setstatus @TraceID, 0;	-- Stop
EXECUTE sp_trace_setstatus @TraceID, 2;	-- Close
 */
GO

/*
CREATE TABLE #LoginAudit	(
	NTUserName			NVARCHAR(256)		Null,
	NTDomainName		NVARCHAR(256)		Null,
	HostName			NVARCHAR(256)		Null,
	ClientProcessID		INT					Null,
	ApplicationName		NVARCHAR(256)		Null,
	LoginName			NVARCHAR(256)		Null,
	SPID				INT					Null,
	ServerName			NVARCHAR(256)		Null,
	EventClass			INT					Null);
DECLARE @TraceFile NVARCHAR(245)= master.DBAdmin.ErrorLogDirectory() + '\' + @@SERVERNAME + '_LoginAudit.trc';
SELECT * FROM ::fn_trace_gettable(@TraceFile, default);
INSERT INTO #LoginAudit SELECT NTUserName, NTDomainName, HostName, ClientProcessID, ApplicationName, LoginName, SPID, ServerName, EventClass FROM ::fn_trace_gettable(@TraceFile, default);
*/
GO